﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DA
{
    // Địa ốc
    public class DiaOcDA:DBConnection
    {
        // hàm tạo
        public DiaOcDA()
        {
            sqlCommand.Connection = sqlConnection;
        }
        // lấy tất cả
        public DataSet GetAll()
        {
            var ds = new DataSet();
            const string query = "select * from DiaOc";
            if (open())
            {
                sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
                sqlDataAdapter.Fill(ds);
               
            }
            close();
            return ds;
        }
          // lấy theo mã địa ốc
        public DataSet GetByID(int id)
        {
            var ds = new DataSet();
            string query = String.Format("select MaDiaOc,TenLoaiDiaOc,DiaChiDiaOc,DienTichDat,DienTichXayDung,Huong,ViTri,MoTa,(CASE TrangThaiBan WHEN 1 THEN N'Đã Bán' else N'Chưa Bán' END) as TrangThaiBan,GiaBan,GiayPhepXayDung,GiayPhepHoanCong,GiayChungNhanQuyenSuDungDat,GiayChungNhanSoHuuNha,HopDongMuaban,(CASE TrangThaiDangKy WHEN 1 THEN N'Đã Đăng Ký' else N'Chưa Đăng Ký' END) as TrangThaiDangKy from DIAOC inner join LOAIDIAOC on DIAOC.MaLoaiDiaOc=LOAIDIAOC.MaLoaiDiaOc  where MaDiaOc = '{0}'", id);
            if (open())
            {
                sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
                sqlDataAdapter.Fill(ds);
            }
            close();
            return ds;
        }
        // thêm 
        public int Add(DiaOcDTO item)
        {
            var query = String.Format("insert into DIAOC values('{0}',N'{1}','{2}','{3}',N'{4}',N'{5}',N'{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}'); select SCOPE_IDENTITY();", 
                item.MaLoaiDiaOc, item.DiaChiDiaOc, item.DienTichDat, 
                item.DienTichXayDung, item.Huong, item.ViTri, item.MoTa, 
            item.TrangThaiBan, item.GiaBan.ToString(), item.GiayPhepXayDung, 
            item.GiayPhepHoanCong, item.GiayChungNhanQuyenSuDungDat, 
            item.GiayChungNhanSoHuuNha, 
            item.HopDongMuaBan, item.MaKH, item.TrangThaiDangKy);

            sqlCommand.CommandText = query;
            try
            {
                open();
                var idRe = -1;
                sqlCommand.CommandText = query;
                var re = sqlCommand.ExecuteScalar().ToString();
                idRe = Int32.Parse(re);
                close();
                return idRe;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return -1;
            }
        }
        public int MaDiaOcTN()
        {
            const string query = "select MAX(MaDiaOc) from DIAOC";
            var id = 0;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    return -1;
                }
                while (sqlDataReader.Read())
                {
                    id = sqlDataReader.GetInt32(0);
                }
                close();
                return id+1;
            }
            catch
            {
                return -1;
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                close();
            }
            return id+1;
        }
        // sửa
        public bool Update(DiaOcDTO item)
        {
            var query = String.Format("Update DIAOC set MaLoaiDiaOc='{0}',DiaChiDiaOc=N'{1}',DienTichDat='{2}',DienTichXayDung='{3}',Huong=N'{4}',ViTri=N'{5}',MoTa=N'{6}',GiaBan='{7}',GiayPhepXayDung='{8}',GiayPhepHoanCong='{9}',GiayChungNhanQuyenSuDungDat='{10}',GiayChungNhanSoHuuNha='{11}',HopDongMuaBan='{12}' where MaDiaOc='{13}'", 
                item.MaLoaiDiaOc, item.DiaChiDiaOc, item.DienTichDat, 
                item.DienTichXayDung, item.Huong, item.ViTri, 
                item.MoTa, item.GiaBan, item.GiayPhepXayDung, 
                item.GiayPhepHoanCong, item.GiayChungNhanQuyenSuDungDat,
                item.GiayChungNhanSoHuuNha, item.HopDongMuaBan, 
                item.MaDiaOc);
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                close();
                return true;
            }
            catch (SqlException s)
            {
                Console.WriteLine(s.Message);
                return false;
            }
        }
        // xóa 
        public bool Delete(int ma_item)
        {
            var query = String.Format("Delete from DiaOc where MaDiaOc = '{0}'", ma_item);
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                close();
                return true;
            }
            catch (SqlException s)
            {
                Console.WriteLine(s.Message);
                return false;
            }
        }
        // đăng ký bán địa ốc
        public bool DangKy(DiaOcDTO diaoc)
        {
            String query = String.Format("update DIAOC set TrangThaiDangKy='1' where MaDiaOc='{0}'", diaoc.MaDiaOc);
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                Console.WriteLine(s.Message);
                return false;
            }
        }
        public bool Ban(string _MaDiaOc)
        {
            MyQueryDA myqueryDA = new MyQueryDA();
            string queryupdate = String.Format("update DIAOC set TrangThaiBan='1' where MaDiaOc='{0}'", _MaDiaOc);
            return myqueryDA.doNonQuery(queryupdate);
        }
    }
}
